﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Business_Objects;

namespace Data_Objects.Implementation
{
    class SqlServerUserDao : IUserDao
    {
        public SqlServerUserDao()
        {
            SQLUtility.TableName = "User";
        }

        //Trả về đối tượng báo ăn
        private User MakeUser(DataRow row)
        {
            int _id = int.Parse(row["UserID"].ToString());
           // Guid _truongID = Guid.Parse(row["TruongID"].ToString());
                  int _schoolID = int.Parse(row["SchoolID"].ToString());
                  string _userName = row["UserName"].ToString();
                  string _password = row["Password"].ToString();
                  string _email = row["Email"].ToString();
                  DateTime _lasActivityDate = DateTime.Parse(row["LasActivityDate"].ToString());
        bool _isAdmin = bool.Parse(row["IsAdmin"].ToString());
        bool _isSystem = bool.Parse(row["IsSystem"].ToString());
        bool _isActive = bool.Parse(row["IsActive"].ToString());

            //Guid _id = ;
            //string _schoolYearNumber = row["SchoolYearNumber"].ToString();
            //string _name = row["SchoolYearName"].ToString();
            //int _year = int.Parse(row["Year"].ToString());
            //int _schoolID = int.Parse(row["SchoolID"].ToString());
        return new User(_id,_userName,_password,_email,_schoolID,_isAdmin,_isSystem,_isActive,_lasActivityDate);
        }

        //Lấy danh sách khách hàng trả về datatable
        private IList<User> MakeUsers(DataTable dt)  
        {
            IList<User> list = new List<User>();
            foreach (DataRow row in dt.Rows)
            {
                list.Add(MakeUser(row));
            }
            return list;
        }
        private DbParameter[] Take(User User)
        {
            DbParameter[] parameters = new DbParameter[9];
            parameters[0] = new SqlParameter("@UserID", User.Id);
            parameters[1] = new SqlParameter("@UserName", User.UserName);
            parameters[2] = new SqlParameter("@Password", User.Password);
            parameters[3] = new SqlParameter("@Email", User.Email);
            parameters[4] = new SqlParameter("@SchoolID", User.SchoolId);

          
            parameters[5] = new SqlParameter("@IsAdmin", User.IsAdmin);

            parameters[6] = new SqlParameter("@IsSystem", User.IsSystem);
            parameters[7] = new SqlParameter("@IsActive", User.IsActive);
            parameters[8] = new SqlParameter("@LasActivityDate", User.LasActivityDate);
            return parameters;
        }
        private DbParameter[] TakeInsert(User User) 
        {
            DbParameter[] parameters = new DbParameter[8];
            
            parameters[0] = new SqlParameter("@UserName", User.UserName);
            parameters[1] = new SqlParameter("@Password", User.Password);
            parameters[2] = new SqlParameter("@Email", User.Email);
            parameters[3] = new SqlParameter("@SchoolID", User.SchoolId);

      
            parameters[4] = new SqlParameter("@IsAdmin", User.IsAdmin);

            parameters[5] = new SqlParameter("@IsSystem", User.IsSystem);
            parameters[6] = new SqlParameter("@IsActive", User.IsActive);
            parameters[7] = new SqlParameter("@LasActivityDate", User.LasActivityDate);
            return parameters;
        }
        public IList<Business_Objects.User> GetUser()
        {
            SQLUtility.TableName = "User";
            DbParameter[] param = null;
            DataTable dt = CommandProvider.GetDatatable(SQLUtility.ProceduresGetAll, param);
            return MakeUsers(dt);
        }

        public Business_Objects.User GetUserByID(int userID)
        {
            SQLUtility.TableName = "User";
            DbParameter[] parameters = new DbParameter[1];
            parameters[0] = new SqlParameter("@UserID", userID);
            DataRow row = CommandProvider.GetDataRow(SQLUtility.ProceduresGetByID, parameters);
            if (row == null) { return null; }
            else
            {
                return MakeUser(row);
            }
        }

        public void InsertUser(Business_Objects.User User)
        {
            SQLUtility.TableName = "User";
            int newID = CommandProvider.Insert(SQLUtility.ProceduresInsert, TakeInsert(User), true);
        }

        public int UpdateUser(Business_Objects.User User)
        {
            SQLUtility.TableName = "User";
            try { return CommandProvider.Update(SQLUtility.ProceduresUpdate, Take(User)); }
            catch { return -1; }
        }

        public int DeleteUser(Business_Objects.User User)
        {
            SQLUtility.TableName = "User";
            try { return CommandProvider.Delete(SQLUtility.ProceduresDelete, Take(User)); }
            catch { return -1; }
        }


        public List<Business_Objects.User> GetUserBySchoolId(int schoolId) 
        {
            DbParameter[] parameters = new DbParameter[1];
            parameters[0] = new SqlParameter("@SchoolID", schoolId);
            DataTable dt = CommandProvider.GetDatatable("usp_SelectUsersByAndSchoolID", parameters);
            return MakeUsers(dt).ToList(); 
        }


        public User GetUserByUserNameAndPassword(string username, string password)
        {
            DbParameter[] parameters = new DbParameter[2];

            parameters[0] = new SqlParameter("@UserName", username);
            parameters[1] = new SqlParameter("@Password", password);
            DataRow row = CommandProvider.GetDataRow("usp_SelectUsersByAndUserNameAndPassword", parameters);
            if (row == null) { return null; }
            else
            {
                return MakeUser(row);
            }
        }
    }
}
